package com.imooc.scala.log

import java.sql.{Connection, DriverManager, PreparedStatement}

import scala.collection.mutable.ListBuffer

object MySQLUtils {
  def getConnection() = {
    DriverManager.getConnection("jdbc:mysql://localhost:3306/scala_train?user=root&password=Zhl@12345678&useSSL=false")
  }

  def release(connect: Connection, pstmt: PreparedStatement) = {
    try {
      if (pstmt != null) {
        pstmt.close()
      }
    } catch {
      case e: Exception => e.printStackTrace()
    }
  }

  def insert(list: ListBuffer[CityStat]) = {
    var connection: Connection = null
    var pstmt: PreparedStatement = null
    try {
      connection = MySQLUtils.getConnection()
      connection.setAutoCommit(false)
      val sql = "insert into city_top(city,times) values(?,?)"
      pstmt = connection.prepareStatement(sql)
      for (ele <- list) {
        pstmt.setString(1, ele.city)
        pstmt.setLong(2, ele.times)
        pstmt.addBatch()
        pstmt.executeBatch()
        connection.commit()
      }
    } catch {
      case e: Exception => e.printStackTrace()
    } finally {
      MySQLUtils.release(connection, pstmt)
    }
  }

  def deleData(day: String) = {
    val tables = Array("city_top")
    var connection: Connection = null
    var pstmt: PreparedStatement = null

    try {
      connection = MySQLUtils.getConnection()
      for (table <- tables) {
        val sql = s"delete from $table where day=?"
        pstmt = connection.prepareStatement(sql)
        pstmt.setString(1, day)
        pstmt.executeUpdate()
      }
    } catch {
      case e: Exception => e.printStackTrace()
    } finally {
      MySQLUtils.release(connection, pstmt)
    }
  }

  def main(args: Array[String]): Unit = {
    println(getConnection())
  }
}
